package com.htrq.util;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;

public class ExportToExcelUtil {
	/**
	 * 导出通用简单内容工作表
	 * @param response
	 * @param request
	 * @param fileName excel文件名
	 * @param sheetname工作表表名
	 * @param titles 工作表表头
	 * @param list 数据
	 * @return
	 * @throws IOException 
	 * @throws Exception
	 */
	public static boolean exportToExcel(HttpServletRequest request, HttpServletResponse response, String fileName,
			String sheetname, String[] titles, List<Object[]> list) throws IOException {
		boolean flag = false;
		OutputStream out = null;
		HSSFWorkbook book = null;
		try {
			String agent = request.getHeader("User-Agent");
			out = response.getOutputStream();
			response.reset();// 输出流
			response.setContentType("application/vnd.ms-excel; charset=utf-8");
			if (StringUtils.isNotBlank(agent) && agent.indexOf("Mozilla") > 0) {
				fileName = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
			} else {
				fileName = toUTF_8String(fileName);
			}
			// "Content-Disposition", "attachment;filename*=utf-8'zh_CN'" zh_CN不可少
			response.setHeader("Content-Disposition", "attachment;filename*=utf-8'zh_CN'" + fileName);
			// 新建工作表
			book = new HSSFWorkbook();
			// LOG.debug("Excel创建成功！");
			HSSFSheet sheet = book.createSheet(sheetname);
			// 设定列的默认宽度
			sheet.setDefaultColumnWidth((short) 14);
			// 合并表格标题单元格
			sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) (titles.length-1)));// 开始行，开始列，结束行，结束列。
			// sheet.addMergedRegion(new CellRangeAddress(0,0,0,0)); //开始行，结束行，开始列，结束列。
			// 单元格样式--通用样式
			HSSFCellStyle style = book.createCellStyle();
			HSSFFont font = book.createFont();
			font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
			font.setFontHeightInPoints((short) 12);
			font.setFontName("微软雅黑");
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
			style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
			style.setFont(font);
			// 创建第一行（也可以称为表头）
			HSSFRow row = sheet.createRow(0);
			row.setHeightInPoints(30);// 第一行的行高
			// 给表头第一行一次创建单元格
			HSSFCell cell = row.createCell((short) 0);
			cell.setCellValue(new HSSFRichTextString("条件查询导出结果"));// 表头的内容
			cell.setCellStyle(style);
			// 创建内容表头header
			HSSFRow row1 = sheet.createRow(1);
			for (int i = 0; i < titles.length; i++) {
				HSSFCell celln = row1.createCell((short) i);
				celln.setCellStyle(style);
				celln.setCellValue(new HSSFRichTextString(titles[i]));
			}
			// 添加数据
			for (int i = 0; i < list.size(); i++) {
				// 获得数据对象
				// Class clsss = obj.getClass();
				// System.out.println(clsss.getName());
				HSSFRow rown = sheet.createRow(i + 2);// 要从第三行开始创建行
				for (int j = 0; j < titles.length; j++) {// 创建单元格
					HSSFCell celln = rown.createCell((short) j);
					celln.setCellStyle(style);
					// celln.setCellValue();
					// 按类型填写数据，不同类型对应不同参数
					// celln.setCellValue("创建单元格");
					Object obj = list.get(i)[j];
					if (obj instanceof Integer) {
						celln.setCellValue((Integer) list.get(i)[j]);
					}
					if (obj instanceof String) {
						celln.setCellValue(new HSSFRichTextString(list.get(i)[j].toString()));
					}
					if (obj instanceof Date) {
						celln.setCellValue(new HSSFRichTextString(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
								.format(list.get(i)[j])));
					}
					if (obj instanceof BigDecimal) {
						celln.setCellValue(new HSSFRichTextString(list.get(i)[j].toString()));
					}
				}
			}
			book.write(out);
			// response.setStatus(200);
			// response.flushBuffer();
			flag = true;
		} catch (IOException ex) {
			System.out.println("导出报表失败!");
		} finally {
			if (out != null) {
				out.close();
			}
		}
		return flag;
	}
	/**
	 * 字符转码
	 * @param s
	 * @return
	 */
	public static String toUTF_8String(String part) {
		try {
			return URLEncoder.encode(part, "UTF-8");
		} catch (UnsupportedEncodingException e) {
			/*
			 * if (LOG.isErrorEnabled()) { LOG.error(ZhjtConstant.SYSTEM_ERROR + e); }
			 */
			System.out.println("字符转换异常");
		}
		return "";
	}
}
